import pymysql
from apscheduler.schedulers.background import BackgroundScheduler
from apscheduler.triggers.interval import IntervalTrigger
from apscheduler.triggers.cron import CronTrigger
import threading
import tkinter as tk
from tkinter import scrolledtext, messagebox
from datetime import datetime

# 全局变量
stop_event = threading.Event()
connections = {}  # 存储连接对象
scheduler = None  # 用于存储调度器实例

# 生成唯一的凭据标识符
def generate_credentials_key(cred):
    return f"{cred['host']}:{cred['port']}:{cred['user']}"

# 获取用户输入的凭据
def get_credentials():
    host = host_entry.get()
    port = int(port_entry.get())
    user = user_entry.get()
    password = password_entry.get()
    return [{'host': host, 'port': port, 'user': user, 'password': password}]

# 连接数据库的函数
def connect_to_database(host, port, user, password):
    connection = pymysql.connect(
        host=host,
        port=port,
        user=user,
        password=password,
        database='',  # 你可以在这里指定数据库名
        charset='utf8',
        cursorclass=pymysql.cursors.DictCursor
    )
    return connection

# 获取当前时间作为日志前缀
def get_current_time():
    return datetime.now().strftime("%Y-%m-%d %H:%M:%S")

# 日志记录函数
def log_message(message, error=False):
    log_area.config(state=tk.NORMAL)
    current_time = get_current_time()
    formatted_message = f"{current_time} - {message}\n"
    if error:
        log_area.insert(tk.END, formatted_message, 'error')
    else:
        log_area.insert(tk.END, formatted_message)
    log_area.see(tk.END)  # 自动滚动到最后一行
    log_area.config(state=tk.DISABLED)

# 清空日志
def clear_log():
    log_area.config(state=tk.NORMAL)
    log_area.delete(1.0, tk.END)
    log_area.config(state=tk.DISABLED)

# 刷新所有连接
def refresh_all_connections():
    global connections
    credentials = get_credentials()

    for cred in credentials:
        cred_key = generate_credentials_key(cred)
        host, port, user, password = cred['host'], cred['port'], cred['user'], cred['password']

        if cred_key not in connections:
            # 第一次连接时，不打印“未找到”日志，直接进行首次连接
            log_message(f"首次连接用户 {user} 的数据库...")
            try:
                connections[cred_key] = connect_to_database(host, port, user, password)
            except Exception as e:
                log_message(f"用户 {user} 连接失败: {e}", error=True)
                continue
        else:
            # 已存在连接，进行刷新
            connection = connections[cred_key]
            log_message(f"正在刷新用户 {user} 的数据库连接...")

            try:
                with connection.cursor() as cursor:
                    cursor.execute("SELECT 1;")
                    log_message(f"用户 {user} 的连接刷新成功。")
            except Exception as e:
                log_message(f"连接刷新失败，尝试重新连接用户 {user}: {e}", error=True)
                connection.close()
                del connections[cred_key]  # 移除失败的连接
                # 重新连接
                log_message(f"重新连接用户 {user}...")
                try:
                    connections[cred_key] = connect_to_database(host, port, user, password)
                except Exception as e:
                    log_message(f"重新连接用户 {user} 失败: {e}", error=True)

# 启动刷新任务
def start_refresh_task():
    clear_log()  # 每次启动刷新任务前，清空日志
    global scheduler
    scheduler = BackgroundScheduler()

    # 启动任务后立即刷新一次
    refresh_all_connections()

    scheduler.add_job(
        refresh_all_connections,
        trigger=IntervalTrigger(minutes=4),  # 每4分钟执行一次
        id='refresh_all_connections_job',
        name='定时刷新所有数据库连接',
        replace_existing=True
    )

    scheduler.add_job(
        stop_refresh_task,
        trigger=CronTrigger(hour=18, minute=30),  # 每天18:30执行一次
        id='stop_refresh_task_job',
        name='每天18:30停止刷新任务',
        replace_existing=True
    )

    scheduler.start()

    start_button.config(state=tk.DISABLED)
    stop_button.config(state=tk.NORMAL)
    log_message("刷新任务已启动，每4分钟刷新一次，且每天18:30自动停止")

# 停止刷新任务
def stop_refresh_task():
    global scheduler
    if scheduler:
        scheduler.remove_all_jobs()  # 移除所有调度任务
        scheduler.shutdown(wait=False)  # 停止调度器
        scheduler = None

    for conn in connections.values():
        conn.close()
    connections.clear()  # 清空连接字典

    start_button.config(state=tk.NORMAL)
    stop_button.config(state=tk.DISABLED)
    log_message("刷新任务已停止，所有数据库连接已关闭。")

# 创建 GUI 界面
root = tk.Tk()
root.title("数据库连接刷新器")

# 创建输入框的 Frame
input_frame = tk.Frame(root, padx=10, pady=10)
input_frame.grid(row=0, column=0, sticky="ew")

# 主机输入
tk.Label(input_frame, text="主机:").grid(row=0, column=0, padx=5, pady=5, sticky="w")
host_entry = tk.Entry(input_frame)
host_entry.grid(row=0, column=1, padx=5, pady=5)

# 端口输入
tk.Label(input_frame, text="端口:").grid(row=1, column=0, padx=5, pady=5, sticky="w")
port_entry = tk.Entry(input_frame)
port_entry.grid(row=1, column=1, padx=5, pady=5)

# 用户名输入
tk.Label(input_frame, text="用户名:").grid(row=2, column=0, padx=5, pady=5, sticky="w")
user_entry = tk.Entry(input_frame)
user_entry.grid(row=2, column=1, padx=5, pady=5)

# 密码输入
tk.Label(input_frame, text="密码:").grid(row=3, column=0, padx=5, pady=5, sticky="w")
password_entry = tk.Entry(input_frame, show='*')
password_entry.grid(row=3, column=1, padx=5, pady=5)

# 创建按钮的 Frame
button_frame = tk.Frame(root, padx=10, pady=10)
button_frame.grid(row=1, column=0, sticky="ew")

# 启动按钮
start_button = tk.Button(button_frame, text="启动刷新任务", command=start_refresh_task, width=15)
start_button.grid(row=0, column=0, padx=5, pady=5)

# 停止按钮
stop_button = tk.Button(button_frame, text="停止刷新任务", command=stop_refresh_task, state=tk.DISABLED, width=15)
stop_button.grid(row=0, column=1, padx=5, pady=5)

# 日志输出区
log_area = scrolledtext.ScrolledText(root, height=10, wrap=tk.WORD, state=tk.DISABLED)
log_area.grid(row=2, column=0, padx=10, pady=10, sticky="nsew")
log_area.tag_config('error', foreground='red')

# 启动 GUI 主循环
root.mainloop()

# 程序退出前确保所有连接关闭
for conn in connections.values():
    conn.close()
print("所有数据库连接已关闭。")


# GUI
# pyinstaller --onefile --noconsole --distpath D:/dabao/output3 --workpath D:/dabao/build_temp3 others/refresh_db_plus_pro.py